const mysql = require('mysql')
const $conf = require('../config/db.js')
const $sql = require('./memberMapping.js')

const pool = mysql.createPool($conf.mysql)

function response(res, result) {
    if(result){
        res.json(result)
    }
}

module.exports = {
    queryCasesByPage(req, res ,next) {
        pool.getConnection((err, connection) => {
            let final = {}
            connection.query(`SELECT * , date_format(update_time,'%Y-%c-%d %h:%i:%s') as updatetime from cases ORDER BY update_time DESC LIMIT ${(req.query.pageNo - 1) * 10},10`, (err, result) => {
                if(!err) {
                    final.list = JSON.parse(JSON.stringify(result))
                }
            })
            connection.query(`select count(*) as total from cases`,(err,result) => {
                if(!err){
                    final = {
                        ...final,
                        total: JSON.parse(JSON.stringify(result))[0].total
                    }
                    connection.release()
                    response(res, final)
                }
            })
        })
    },
    queryCaseById(req, res, next) {
        pool.getConnection((err,connection) => {
            connection.query(`select *,date_format(accept_time,'%Y-%c-%d') as accepttime from cases where id=${req.query.id}`, (err, result) => {
                if (!err) {
                    response(res, result)
                    connection.release()
                }
                
            })
        })
    },
    queryMajorCases(req, res, next) {
        pool.getConnection((err,connection) => {
            connection.query(`select *,date_format(update_time,'%Y-%c-%d') as updatetime from cases where is_major=1`, (err, result) => {
                if (!err) {
                    response(res, result)
                    connection.release()
                }
                
            })
        })
    }
}